{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Help Desk - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "Show the manager and number of calls received for each hour of the day on 2017-08-12\n",
    "\n",
    "```\n",
    "+---------+---------------+----+\n",
    "| Manager | Hr            | cc |\n",
    "+---------+---------------+----+\n",
    "| LB1     | 2017-08-12 08 |  6 |\n",
    "| LB1     | 2017-08-12 09 | 16 |\n",
    "| LB1     | 2017-08-12 10 | 11 |\n",
    "| LB1     | 2017-08-12 11 |  6 |\n",
    "| LB1     | 2017-08-12 12 |  8 |\n",
    "| LB1     | 2017-08-12 13 |  4 |\n",
    "| AE1     | 2017-08-12 14 | 12 |\n",
    "| AE1     | 2017-08-12 15 |  8 |\n",
    "| AE1     | 2017-08-12 16 |  8 |\n",
    "| AE1     | 2017-08-12 17 |  7 |\n",
    "| AE1     | 2017-08-12 19 |  5 |\n",
    "+---------+---------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "11 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Manager</th>\n",
       "        <th>Hr</th>\n",
       "        <th>cc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB1</td>\n",
       "        <td>2017-08-12 08</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB1</td>\n",
       "        <td>2017-08-12 09</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB1</td>\n",
       "        <td>2017-08-12 10</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB1</td>\n",
       "        <td>2017-08-12 11</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB1</td>\n",
       "        <td>2017-08-12 12</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB1</td>\n",
       "        <td>2017-08-12 13</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AE1</td>\n",
       "        <td>2017-08-12 14</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AE1</td>\n",
       "        <td>2017-08-12 15</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AE1</td>\n",
       "        <td>2017-08-12 16</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AE1</td>\n",
       "        <td>2017-08-12 17</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AE1</td>\n",
       "        <td>2017-08-12 19</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LB1', '2017-08-12 08', 6),\n",
       " ('LB1', '2017-08-12 09', 16),\n",
       " ('LB1', '2017-08-12 10', 11),\n",
       " ('LB1', '2017-08-12 11', 6),\n",
       " ('LB1', '2017-08-12 12', 8),\n",
       " ('LB1', '2017-08-12 13', 4),\n",
       " ('AE1', '2017-08-12 14', 12),\n",
       " ('AE1', '2017-08-12 15', 8),\n",
       " ('AE1', '2017-08-12 16', 8),\n",
       " ('AE1', '2017-08-12 17', 7),\n",
       " ('AE1', '2017-08-12 19', 5)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- In MySQl, date_format(Call_date, '%Y-%m-%d %H') AS Hr\n",
    "SELECT \"Manager\", TO_CHAR(\"Call_date\", 'YYYY-MM-dd HH24') \"Hr\", COUNT(\"Call_ref\") cc\n",
    "  FROM \"Issue\" JOIN \"Shift\" ON (\"Issue\".\"Taken_by\"=\"Shift\".\"Operator\" AND Date(\"Issue\".\"Call_date\")=\"Shift\".\"Shift_date\")\n",
    "    WHERE Date(\"Call_date\")='2017-08-12'\n",
    "    GROUP BY \"Manager\", \"Hr\"\n",
    "    ORDER BY \"Hr\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**80/20 rule. It is said that 80% of the calls are generated by 20% of the callers. Is this true? What percentage of calls are generated by the most active 20% of callers.**\n",
    "\n",
    "Note - Andrew has not managed to do this in one query - but he believes it is possible.\n",
    "\n",
    "```\n",
    "+---------+\n",
    "| t20pc   |\n",
    "+---------+\n",
    "| 32.2581 |\n",
    "+---------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>t20pc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32.2581</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('32.2581'),)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ROUND(100 * SUM(a.n)/(SELECT COUNT(*) FROM \"Issue\"), 4) t20pc FROM\n",
    "  (SELECT ROW_NUMBER() OVER (ORDER BY COUNT(\"Call_ref\") DESC) rn,\n",
    "   \"Caller_id\", COUNT(\"Call_ref\") n\n",
    "   FROM \"Issue\"\n",
    "   GROUP By \"Caller_id\"\n",
    "   ORDER BY n DESC\n",
    "  ) AS a\n",
    "WHERE a.rn <= (\n",
    "    SELECT COUNT(*) FROM (SELECT DISTINCT \"Caller_id\" FROM \"Issue\") AS b) * 0.2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**Annoying customers. Customers who call in the last five minutes of a shift are annoying. Find the most active customer who has never been annoying.**\n",
    "\n",
    "```\n",
    "+--------------+------+\n",
    "| Company_name | abna |\n",
    "+--------------+------+\n",
    "| High and Co. |   20 |\n",
    "+--------------+------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Company_name</th>\n",
       "        <th>abna</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>High and Co.</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('High and Co.', 20)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Company_name\", COUNT(*) abna\n",
    "FROM \"Issue\" JOIN \"Caller\" ON \"Issue\".\"Caller_id\"=\"Caller\".\"Caller_id\"\n",
    "   JOIN \"Customer\" ON \"Caller\".\"Company_ref\"=\"Customer\".\"Company_ref\"\n",
    "WHERE \"Customer\".\"Company_ref\" NOT IN\n",
    "(\n",
    " SELECT \"Customer\".\"Company_ref\"\n",
    " FROM (\"Issue\" JOIN \"Shift\" ON (\"Issue\".\"Taken_by\"=\"Shift\".\"Operator\" AND Date(\"Call_date\")=\"Shift\".\"Shift_date\")\n",
    "   JOIN \"Shift_type\" ON \"Shift\".\"Shift_type\"=\"Shift_type\".\"Shift_type\"\n",
    "   LEFT JOIN \"Caller\" ON \"Issue\".\"Caller_id\"=\"Caller\".\"Caller_id\"\n",
    "   LEFT JOIN \"Customer\" ON \"Caller\".\"Company_ref\"=\"Customer\".\"Company_ref\")\n",
    " WHERE CONCAT(\"Shift_date\", ' ', \"End_time\")::timestamp-\"Call_date\" <= interval '00:05'\n",
    " -- In MySQL: timestampdiff(MINUTE, Call_date, CONCAT(Shift_date, ' ', End_time)) between 0 and 5\n",
    ")\n",
    "GROUP BY \"Customer\".\"Company_ref\"\n",
    "ORDER BY COUNT(*) DESC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Maximal usage. If every caller registered with a customer makes a call in one day then that customer has \"maximal usage\" of the service. List the maximal customers for 2017-08-13.**\n",
    "\n",
    "```\n",
    "+-------------------+--------------+-------------+\n",
    "| company_name      | caller_count | issue_count |\n",
    "+-------------------+--------------+-------------+\n",
    "| Askew Inc.        |            2 |           2 |\n",
    "| Bai Services      |            2 |           2 |\n",
    "| Dasher Services   |            3 |           3 |\n",
    "| High and Co.      |            5 |           5 |\n",
    "| Lady Retail       |            4 |           4 |\n",
    "| Packman Shipping  |            3 |           3 |\n",
    "| Pitiable Shipping |            2 |           2 |\n",
    "| Whale Shipping    |            2 |           2 |\n",
    "+-------------------+--------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>company_name</th>\n",
       "        <th>caller_count</th>\n",
       "        <th>issue_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Askew Inc.</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bai Services</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dasher Services</td>\n",
       "        <td>3</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>High and Co.</td>\n",
       "        <td>5</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lady Retail</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Packman Shipping</td>\n",
       "        <td>3</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pitiable Shipping</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Whale Shipping</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Askew Inc.', 2, 2),\n",
       " ('Bai Services', 2, 2),\n",
       " ('Dasher Services', 3, 3),\n",
       " ('High and Co.', 5, 5),\n",
       " ('Lady Retail', 4, 4),\n",
       " ('Packman Shipping', 3, 3),\n",
       " ('Pitiable Shipping', 2, 2),\n",
       " ('Whale Shipping', 2, 2)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT a.company_name, COUNT(*) caller_count, SUM(n) issue_count FROM\n",
    "(\n",
    " SELECT \"Customer\".\"Company_ref\", \"Customer\".\"Company_name\" company_name, \n",
    "    \"Caller\".\"Caller_id\", \n",
    "    CASE WHEN COUNT(\"Call_ref\")>0 THEN 1 ELSE 0 END n\n",
    " FROM ((SELECT * FROM \"Issue\" WHERE Date(\"Call_date\")='2017-08-13') AS issue \n",
    "       RIGHT JOIN \"Caller\" ON issue.\"Caller_id\"=\"Caller\".\"Caller_id\")\n",
    "    LEFT JOIN \"Customer\" ON \"Caller\".\"Company_ref\"=\"Customer\".\"Company_ref\"\n",
    "    GROUP BY \"Customer\".\"Company_ref\", \"Caller\".\"Caller_id\"\n",
    ") AS a\n",
    "GROUP BY a.company_name\n",
    "HAVING COUNT(*)=SUM(n)\n",
    "ORDER BY a.company_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Consecutive calls occur when an operator deals with two callers within 10 minutes. Find the longest sequence of consecutive calls – give the name of the operator and the first and last call date in the sequence.**\n",
    "\n",
    "```\n",
    "+----------+---------------------+---------------------+-------+\n",
    "| taken_by | first_call          | last_call           | calls |\n",
    "+----------+---------------------+---------------------+-------+\n",
    "| AB1      | 2017-08-14 09:06:00 | 2017-08-14 10:17:00 |    24 |\n",
    "+----------+---------------------+---------------------+-------+\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Solution in MySQL_:\n",
    "\n",
    "```sql\n",
    "SELECT a.taken_by, a.first_call, a.call_date AS last_call, a.call_count AS calls\n",
    "FROM\n",
    "(SELECT Issue.taken_by, \n",
    "       Issue.call_date,\n",
    "       @counter := CASE WHEN TIMESTAMPDIFF(MINUTE, @current_call, Issue.call_date) <= 10\n",
    "                          THEN @counter + 1\n",
    "                        ELSE 1\n",
    "                   END AS call_count,\n",
    "       @first_call := CASE WHEN @counter = 1\n",
    "                             THEN @first_call := call_date\n",
    "                           ELSE @first_call\n",
    "                      END AS first_call,\n",
    "       @current_call := Issue.call_date\n",
    "FROM Issue,\n",
    "(SELECT @counter := 0, @first_call := 0, @current_call := 0) AS initvar\n",
    "ORDER BY Issue.taken_by, Issue.call_date) AS a\n",
    "ORDER BY a.call_count DESC\n",
    "LIMIT 1;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Taken_by</th>\n",
       "        <th>first_call</th>\n",
       "        <th>last_call</th>\n",
       "        <th>n_calls</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AB1</td>\n",
       "        <td>2017-08-14 09:06:00</td>\n",
       "        <td>2017-08-14 10:17:00</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AB1', datetime.datetime(2017, 8, 14, 9, 6), datetime.datetime(2017, 8, 14, 10, 17), 24)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS(\n",
    "-- label consecutive calls 0\n",
    "  SELECT \"Issue\".*, \n",
    "    CASE WHEN \"Call_date\" - LAG(\"Call_date\", 1) OVER (\n",
    "        PARTITION BY \"Taken_by\" ORDER BY \"Call_date\")> INTERVAL '10 minute' THEN 1 \n",
    "         ELSE 0 END flag\n",
    "    FROM \"Issue\"\n",
    "), g AS (\n",
    "-- cumsum the flags for grouping\n",
    "  SELECT t.*, SUM(t.flag) OVER (\n",
    "      PARTITION BY t.\"Taken_by\" ORDER BY t.\"Call_date\") AS grp\n",
    "    FROM t\n",
    "), rslt AS (\n",
    "-- aggregate\n",
    "  SELECT \"Taken_by\", grp, MIN(\"Call_date\") first_call, \n",
    "    MAX(\"Call_date\") last_call, COUNT(\"Caller_id\") n_calls\n",
    "    FROM g\n",
    "    GROUP BY \"Taken_by\", grp\n",
    ")\n",
    "SELECT \"Taken_by\", first_call, last_call, n_calls\n",
    "  FROM rslt WHERE n_calls=(SELECT MAX(n_calls) FROM rslt);"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
